Mel Schwan, Stuart Miller, Justin Howard, Paul Adams¶

Lab One: Visualization and Data Preprocessing¶

MiniLab1 Project Requirments -¶

1. Business understanding

   1. Describe the purpose of the data set you selected
   2. Describe how you would define and measure the outcomes from the dataset
   3. How would you measure the effectiveness of a good prediction algorithm

2. Data Understanding

   1. Describe the meaning and type of data for each attribute in the data file
   2. Verify data quality: Explain any missing values, duplicate data, and outliers
   3. Give simple, appropriate statistics (range, mode, mean, median, variance, counts, etc.) for the most important attributes and describe what they mean
   4. Visualize the most important attributes appropriately (at least 5 attributes)
   5. Explore relationships between attributes: Look at the attributes via scatter plots, correlation, cross-tabulation, group-wise averages, etc. as appropriate
   6. Identify and explain interesting relationships between features and the class you are trying to predict
   7. Are there other features that could be added to the data or created from existing features? Which ones?

CRISP-DM stands for cross-industry process for data mining. It provides a structured approach to planning a data mining project. It is a robust and well-proven methodology.

For Lab One we will use the first two portions of this methodology. We will start by focusing on understanding the objectives and requirements from a business perspective, and then using this knowledge to define the data problem and project plan. Next we will identify data quality issues, discover initial insights, or to detect interesting nuggets of information that might for a hypothesis for analysis.

In future labs we will execute all of the CRISP-DM steps.

1. Stage One - Determine Business Objectives and Assess the Situation ¶

The first stage of the CRISP-DM process is to understand what you want to accomplish from a business perspective. We will define our objectives and constraints that must be properly balanced. The goal of this stage of the process is to uncover important factors that could influence the outcome of our project.

1.1 Assess the Current Situation (Q1A)¶

This analysis of Home Credit's Default Risk dataset will focus on generating accurate loan default risk probabilities. Predicting loan defaults is essential to the profitability of banks and, given the competitive nature of the loan market, a bank that collects the right data can offer and service more loans. The target variable of the dataset is the binary label, 'TARGET', indicating whether the loan entered into default status or not.

1.1.1. Inventory of resources ¶

List the resources available to the project including:

• Personnel:
  • Mel Schwan
  • Stuart Miller
  • Justin Howard
  • Paul Adams
• Data:
  • 307,511 individual loans
  • application_{train|test}.csv
  • bureau.csv
  • bureau_balance.csv
  • POS_CASH_balance.csv
  • credit_card_balance.csv
  • previous_application.csv
  • installments_payments.csv
  • HomeCredit_columns_description.csv |
• Computing resources:
  • Four personnel computers
• Software:
  • Jupyter Notebook
  • Library Packages
    • Pandas
    • Pandas_profile
    • Seaborn
    • Numpy
    • Mathplotlib
    • Tables
    • Cleaning (custom package)
    • Sklearn

1.1.2. Requirements - ¶

• Requirements
  • Perform analysis of a data set: exploring the statistical summaries of the features, visualizing the attributes, and making conclusions from the visualizations and analysis. Follow the CRISP-DM framework in your analysis (you are not performing all of the CRISP-DM outline)
  • Demostrate the teams understanding of Homecredits Business and the data model to best predict default on loans

1.1.3.Risks and contingencies ¶

• Imputation of data may result in false interpetation of the real data values
• Elimintation of features can eliminate key factors in predicting loan defaults

1.1.4.Terminology ¶

• Assets - Valuable resources owned by a business, which were acquired at a measurable money cost.
• Bad debt - Debts that are not collectible and therefore, proves to be of no worth to the creditor.
• Bridge Loan - Refers to short-term loan to fund temporary needs as long as permanent financing is not available.
• Capitalization Rate - A rate of interest (discount) employed to calculate the present value of future cash flows. This is the rate that investors expect.
• Credit Analysis - The process of determining whether an applicant satisfies the credit standards of a firm and the amount of credit that should be extended to him.
• Debt Service Coverage Ratio - Ratio of total cash operating funds available to service debt obligations.
• Default Risk - Uncertainty of expected returns from a security attributable to possible changes in the financial capacity of the issuer to make future payments to the security owner.
• Delinquent - A situation where one fails to make payment, which can lead to foreclosure.
• Line of Credit - A bank agreement stating a company may borrow at any time up to the specified limit.
• Net Working Capital/Net Worth - The excess of current assets over current liabilities.Negotiable
  • Certificates of Deposits
  • It is a marketable receipt of funds deposited in a bank for a fixed period.
  • Off-Balance Sheet Financing
  • Hidden form of debt without being shown as a liability.
• Principal - An amount of money upon which interest is calculated.
• Roll Over - A situation where parties to the contract agree to carry over the loan for another stated period at the time of maturity.
• Security - Any thing offered or given to fulfill the performance of a contract. For example, real estate, stocks, fixed assets, jewelry, etc.
• Term - The tenure of the policy.
• Working capital - Current Assets plus Current Liabilities.
• Write off - To consider as a loss or failure in case of uncollectible investments.

1.1.5.Costs and benefits (Q1B) ¶

• Cost

  • Many people struggle to get loans due to insufficient or non-existent credit histories. And, unfortunately, this population is often taken advantage of by untrustworthy lenders.

• Benefits

  • Home Credit strives to broaden financial inclusion for the unbanked population by providing a positive and safe borrowing experience. In order to make sure this underserved population has a positive loan experience, Home Credit makes use of a variety of alternative data--including telco and transactional information--to predict their clients' repayment abilities.

1.2 What are the desired outputs of the project? (Q1C) ¶

Business success criteria

• Cost
• Reduce default rates to 10 precent

Data mining success criteria

• Build a data model that has a ROC value of .80 - .90

1.3 What Questions Are We Trying To Answer? ¶

Use Home Credit current customer data to predict whether a potential client is capable of repayment of the loan requested. During this process we will determine the features that are most influencial in determining this target variable.

2. Stage Two - Data Understanding ¶

The second stage of the CRISP-DM process requires you to acquire the data listed in the project resources. This initial collection includes data loading, if this is necessary for data understanding. For example, if you use a specific tool for data understanding, it makes perfect sense to load your data into this tool. If you acquire multiple data sources then you need to consider how and when you're going to integrate these.

2.1 Initial Data Report (Q2) .¶

Initial data collection report - List the data sources acquired together with their locations, the methods used to acquire them and any problems encountered. Record problems you encountered and any resolutions achieved. This will help both with future replication of this project and with the execution of similar future projects.

2.2 Describe Data (Q2A) ¶

Data description report - Describe the data that has been acquired including its format, its quantity (for example, the number of records and fields in each table), the identities of the fields and any other surface features which have been discovered. Evaluate whether the data acquired satisfies your requirements.

We will use two of the files from the total dataset.

• application_train.csv: Information provided with each loan application
• bureau.csv: Information regarding clients from the credit bureaus

The two data files can be joined on the loan id (SK_ID_CURR).

Loan Application Features¶

There are 122 features and 307511 observations in application_train.csv.

The following features are loan application attributes from application_train.csv. Descriptions, types, and and units are given for each feature. In the original dataset, there were a large number of features related to building features included with the loan applications. However, these features could not be used due to the large number of missing values.

Bureau Data Features¶

Features from the bureau data are not used directly; new features are engineer. Descriptions for the base features that are used in feature engineering are described here. For details on the engineered features see section 2.5 Feature Engineering.

2.3 Verify Data Quality ¶

Note: All this resolutions to data quality described in this section are implemented in cleaning.py, which is used to load the cleaned dataset.

2.3.1. Missing Data ¶

In addition to incorrect datatypes, another common problem when dealing with real-world data is missing values. These can arise for many reasons and have to be either filled in or removed before we train a machine learning model. First, let’s get a sense of how many missing values are in each column

While we always want to be careful about removing information, if a column has a high percentage of missing values, then it probably will not be useful to our model. The threshold for removing columns should depend on the problem

Analysis of Missing Data in Each Feature¶

Each feature or set of features will be discussed in descending order of the rate of missing values.

Building Features with Missing Values
A large number of the features with values are normlaized infromation about the build where the client lives (such as ENTRANCES_MODE or COMMONAREA_AVG). It is plausible that only some of these features exist in a given client's building. It is also plausible that some of the records are missing (not provided by the client). There does not appear to be an indication if values are missing or not applicable to the client. All of these values have a missing rate above 40%.

Missing values in OWN_CAR_AGE
This feature refers to the age of cars owned by the client. Approximately 66.0% of the values are empty. However, there is also a feature FLAG_OWN_CAR, which indicates that the client owns a car. It is reasonable to expect that values will be missing if the client does not own a car. For clients reporting owning a car, all but 5 car age values are present. For clients reporting not owning a car, no car ages are reported. Since the missing rate is actually very small, these missing values could be imputed.

Missing values in EXT_SOURCE_1, EXT_SOURCE_2, and EXT_SOURCE_3
Since there are zeros in these columns, we expect that the missing values represent lack of the external source these clients. Therefore, we will add an encoding feature EXT_SOURCE_<number>_AV for each of the external source features that represents the presence of external information. These original columns will only be used as an interaction with these encoding features and the missing values will be filled with zero.

• EXT_SOURCE_1 has a missing rate of 56.4%
• EXT_SOURCE_2 has a missing rate of 0.2%
• EXT_SOURCE_3 has a missing rate of 19.8%

Missing values in OCCUPATION_TYPE
There does not appear to be a indication that OCCUPATION_TYPE is systematically missing. We will assume that the client did not provide this information and impute with a new categorical level Unknown. OCCUPATION_TYPE has a missing rate of 31.3%.

Missing values in ORGANIZATION_TYPE
Later in the outliers section, it is shown that the NAs in ORGANIZATION_TYPE are associated with occupations listed as 'Unemployed' or 'Pensioners'. We will take this to mean that these clients are not assocated with any employer and will impute these NAs with 'None'. ORGANIZATION_TYPE has a missing rate of 18%.

Missing values for AMT_REQ_CREDIT_BUREAU Features
These features indicate the number of credit enquiries on the client in a given time period: hour, day, week, month, quarter, and year. There does not appear to be a systematic reason the missing values in these features. We will treat these records as if there are no credit enquires for these records and impute with zero. These features have a missing rate of 13.5%.

Remaining Features with Low Missing Rate
The remaining missing features are missing at rates below 0.5%. Imputation on these features should have only a small impact on the total dataset. We will use standard imputation strategies for these features: imputation of the mode for categorical features and imputation of the median for continuous features. We are imputing with continuous features median rather than mode because the continuous features are skewed and median is not impacted by large values in the features.

2.3.2. Outliers ¶

At this point, we may also want to remove outliers. These can be due to typos in data entry, mistakes in units, or they could be legitimate but extreme values. For this project, we will remove anomalies based on the definition of extreme outliers:

https://www.itl.nist.gov/div898/handbook/prc/section1/prc16.htm

• Below the first quartile − 3 ∗ interquartile range
• Above the third quartile + 3 ∗ interquartile range

High Values in DAYS_EMPLOYED¶

There are a large number of entries for DAYS_EMPLOYED outside the main distribution. These entries are at value 365243 and there are 55374 instances.

These high values for DAYS_EMPLOYED appear to be associated with clients that are "Pensioners" or Unemployed and do not list an employment orgainization (ORGANIZATION_TYPE).

Possible Solution

A possible solution to this data quality issue would be to add a None level to ORGANIZATION_TYPE and only use DAYS_EMPLOYED as an interaction with ORGANIZATION_TYPE.

Data Cleaning Script¶

All the cleaning discussed in the sections above are implemented in cleaning.py. This script contains a function (read_clean_data) to apply the cleaning steps and return the cleaned dataset for work.

Details

• Cleaning
  • Read csv with Pandas (setting correct data types)
  • Drop columns that will not be used
  • Recode NA values that are not listed as np.nan
  • Formattings
  • Encode categorical variables
• Returns
  • DataFrame with cleaned data

Sample Output

2.4 Cleaned Dataset Simple Statistics Profile (Q2C) ¶

2.4.1 Distributions (Q2D) ¶

This part of the exploration focused on the use of box plots and histograms for visualing continuous variables and bar charts for visualizing categorical variables. Variables we expect to be important to the important to analysis were selected for univariate visualization.

Financial Features

We expect that financial features will be important to the analysis. We expect that features such as AMT_INCOME_TOTAL would be relevant for a client's ability to payback a loan. Additionally, a client's income, especially large values and small values may influence a bank's willness to provide credit to a client. However, clients with extremely large income may receive special treatment due to the potential value.

Due to the skewed nature of these distributions, a histgram and a boxplot are shown. The histogram shows the features of the main distribution, while the behavior of the tails and extreme values are shown in the boxplots.

AMT_INCOME_TOTAL

For ease of visualization, AMT_INCOME_TOTAL was transformed with log base-10. This is due to influence from some very large observations on histogram binning. As shown in the pair of plots for AMT_INCOME_TOTAL, most of the values are clustered just above 100,000 (5). The main section of the log transformed distribution appears to relatively close to a normal (the distribution is right-skewed on the original scale). Several extreme values are shown in the box plot. The most extreme value is approximately 1000 times larger than the median and mean of the distribution.

AMT_ANNUITY

Like AMT_INCOME_TOTAL, AMT_ANNUITY has a right skewed distribution. However, this feature could be plotted on the original scale because the scale is not as large as AMT_INCOME_TOTAL. The histogram shows that most of the values are between 0 and 50,000. The boxplot shows that there are some exteme values outside the main distribution. The largest extreme value is approximately 10 times the median value.

For the finacial features we expect that some kind of transformation may be necessary to reduce the influence of the extreme values. We may also consider removing very large outliers as they may be treated differently by the bank. For instance, clients with very large incomes may be work personally with a bank representative rather than interacting with an automated system.

DAYS_BIRTH

We expect that client age may be an important factor in determining a client's ability to pay back a loan. Higher ages may represent more time to accumulate wealth and more experience dealing with financial matter. The ages are transformed into years (the original scale is days) because years is a more interpretable unit than days when the values are large. Client ages appear to be almost uniform. The majoity of clients are around 25 to 65 years of age.

DAYS_EMPLOYED

We expect that tenture may be useful in predicting loan repayment ability. In some cases, individuals that have stayed with an employer for a long period of time are compensated well. Since values are large, the tenure with most recent employer are plotted in years rather than days. Tenure with current employer is very right-skewed. There is a steep decrease in clients tenure from 0 years to approximately 15 years. There appears to be an inflection point around 15 years of tenure and the rate of decrease of the distribution tramatically slows. Clients that were listed as pensioners or unemployed were filtered out of this distribution.

NAME_HOUSING_TYPE

Almost all clients reported living in a house or apartment. Only a relatively small portion of clients reported in other types of living situations. The skewness of this categorical variable could be an issue because of the bias toward a single response.

OCCUPATION_TYPE

We expect that the client's type of occupation may affect a client's ability to repay loans. The bar chart shows the counts of occupation type reported by clients. We see that many occupation types were reported. This feature has a high cardinality with low counts in about two thirds in of the categories. Oddly, a large number did not report an occuption type; these are listed as "Unknown." It is possible that too many clients reported no type of occupation for this feature to be useful.

2.4.2 Correlations (Q2E) ¶

To begin the process of exploring the relationships between continuous variables, we rely on two strategies:

• Scatterplot Matrices
• Correlation Plots Scatterplot matrices are intended to help visually identify relationships, and evidence of multicollinearity, between predictor variables. Correlation Plots, or heatmaps annotated with the Pearson's Correlation Coefficient of the two predictor variables, will quantify the relationship between the predictors in question. Together, these visualizations provide an initial form of feature selection because relationships between variables will be limited and the relationships to the predictor variable will be maximized.

A limitation of this strategy is that scatterplot matrices can only visualize a limited number of variables at once, but a deeper mathmatical exploration in the form of Component Analysis will be performed as well.

To analyze the distribution of the target variable across the categorical variables, we rely primarily on:

• Stacked Bar Charts Stacked bar charts are an ideal way of analyzing the distribution of the target variable across the continuous variables.

Additionally, we will view the relationship between the continuous variables and the target variable scatterplot matrices colored by group membership, where the groups are the target variable. This visualization method will help identify variables that provide the greatest discrimination between the two levels of TARGET.

Scatterplot Matrices of Coninuous Variables

• To begin, we will user scatterplot matrices to identify relationships between groups of variables, without the TARGET variable.

Outliers Identified¶

The scatterplot matrix revealed the impact of significant outliers in the AMT_INCOME_TOTAL feature. The most significant outlier is more than ten times that of the next greatest income.

Other Significant Findings¶

A collinear relationship between AMT_GOODS_PRICE and AMT_CREDIT also exists, representing the fact that cash credit is awarded on the basis of the value of the goods purchased. Also, the AMT_CREDIT, AMT_ANNUITY, and AMT_GOODS_PRICE variables appear to have almost identical relationships. The AMT_GOODS_PRICE feature will be considered for elimination, but kept in the training set until the component analysis is complete.

To observe the behavior of applicants based on income, we will bin them into quantiles using the AMT_INCOME_TOTAL feature and plot them against TARGET.

We chose to visualize the distribution of loan defaults across income quantiles with a heatmap because it will quickly allow us to assess the distribution of the quantiles and identify the quantiles where the majority of defaults exist in. This knowledge will help determine whether removing the high income outliers from consideration will be detrimental to the model's ability to discriminate between the two levels of the TARGET variable.

A heatmap of loan defaults by income brackets indicates that a majority of loan defaults occur from the lower end of the income spectrum, within the range of 25,649 and 112,500. This observation points to a modest impact on a model where the highest income earners are removed from consideration.

Correlation Plot¶

A heatmap is chosen to visualize the correlation matrix of the large scale continuous variables because of the ease of determining multicollinearity among the predictor variables.

The correlation plot reveals a 99% correlation between the AMT_GOODS_CREDIT and AMT_CREDIT features. AMT_GOODS_CREDIT will be removed from consideration because it is collinear with AMT_ANNUITY as well as AMT_CREDIT and will cause interference as we try to determine feature importance.

The kernel density plots reveal that applicants with fewer active accounts, total accounts, and less available credit tend to have a higher risk of defaulting on loans, as expected. The outlier default whose income was in excess of 117 million dollars is also clearly evident in most of the plots and would likely have a strong impact on a final model. This provides more evidence for the removal of the millionaire default outlier.

When analyzing the distribution of hte TARGET variable across the continuous credit features, the impact of the outlier is even more pronounced, making it impossible to view the features that will help identify features critical to predicting defaults.

This barplot reveals that a vast majority of the loans in the dataset are cash loans offered to people who own their house or apartment.

Differencing¶

Differencing Specifically, a new series is constructed where the value at the current time step is calculated as the difference between the original observation and the observation at the previous time step. value(t) = observation(t) - observation(t-1)

2.5 Feature Engineering (Q2G) ¶

Engineered Loan Application Features¶

The following features were engineered from the loan application features (from application_train.csv).

The following features were engineered from the bureau features (from bureau.csv).